package com.jing.spring_boot_demo.provider.rest.excel;

import com.jing.spring_boot_demo.provider.commons.entity.User;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

// 将数据导出到 excel 工作簿
public class ExportExcel {

    public static void main(String[] args) {
        FileOutputStream fos = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            File file = new File("E:\\" + "导入测试.xlsx");
            if (!file.exists()){
                System.out.println("指定的文件不存在，自动创建该文件");
                file.createNewFile();
            }
            // 获取指定本地文件的输入流对象
            fos = new FileOutputStream(file);
            // 创建一个工作簿对象
            workbook = new XSSFWorkbook();
            // 创建一个工作表 sheet
            sheet = workbook.createSheet("test");
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 模拟获取的用户数据
        List<User> users = saveUser();
        // 合并单元格，构造参数依次表示起始行，截至行，起始列， 截至列
        CellRangeAddress region = new CellRangeAddress(0, 0, 2, 3);
        assert sheet != null;
        sheet.addMergedRegion(region);
        XSSFCellStyle style= workbook.createCellStyle();
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置单元格标题
        XSSFRow row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("主键");
        row1.createCell(1).setCellValue("名字");
        row1.createCell(2).setCellValue("密码");
        row1.createCell(4).setCellValue("备注");

        for(int i = 1;i < users.size();i++){
            //行
            XSSFRow row = sheet.createRow(i);
            //对列赋值
            row.createCell(0).setCellValue(users.get(i).getId());
            row.createCell(1).setCellValue(users.get(i).getName());
            row.createCell(2).setCellValue(users.get(i).getPassword());
            row.createCell(4).setCellValue(users.get(i).getRemark());
        }
        try {
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static List<User> saveUser(){
        List<User> users = new ArrayList<>();
        users.add(new User(101, "小红", "abc", "测试"));
        users.add(new User(102, "小红", "abc", "测试"));
        users.add(new User(103, "小红", "abc", "测试"));
        users.add(new User(104, "小红", "abc", "测试"));
        users.add(new User(105, "小红", "abc", "测试"));
        users.add(new User(106, "小红", "abc", "测试"));
        return users;
    }
}
